package unitests.com.test.src;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

public class SalesmanDAO {

	private String ip;
	private String db;
	private String username;
	private String password;
	private Connection connection;
	
	public SalesmanDAO(String ip, String db, String username, String password) throws Exception{
		this.ip = ip;
		this.db = db;
		this.username = username;
		this.password = password;
		this.connection = ConnectionManager.getConnection(ip, db, username, password);
	}

	

	//Create
	public void insert(/*Salesman salesman*/) {
		Salesman salesman = null;
		try {
			Object deserializedObject = SerializationUtil.deserialize("c:\\salesman.out");
			salesman = (Salesman) deserializedObject;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement("INSERT INTO tblSalesmans id,name,pswrd,role,is_connected)" +
					"VALUES (?, ?, ?, ?, ?)");
			preparedStatement.setInt(1, salesman.getId());
			preparedStatement.setString(2, salesman.getName());
			preparedStatement.setString(3, salesman.getPswrd());
			preparedStatement.setString(4, salesman.getRole());
			preparedStatement.setInt(5, salesman.getIsConnected());
			preparedStatement.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			//ConnectionManager.closeConnection(connection);
		}
	}

	public Salesman selectById(int id) {
		Salesman salesman = new Salesman();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
 
        try {
            preparedStatement = connection.prepareStatement("SELECT * FROM tblSalesmans WHERE id = ?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
 
            while (resultSet.next()) {
            	salesman.setId(resultSet.getInt("id"));
            	salesman.setName(resultSet.getString("name"));
            	salesman.setPswrd(resultSet.getString("pswrd"));
            	salesman.setRole(resultSet.getString("role"));
            	salesman.setIsConnected(resultSet.getInt("is_connected"));
            }
 
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            //ConnectionManager.closeConnection(connection);
        }
 
        return salesman;
    }

	public Salesman selectByNameAndPswrd(String name, String pswrd) {
		Salesman salesman = new Salesman();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
 
        try {
            preparedStatement = connection.prepareStatement("SELECT * FROM tblSalesmans WHERE name = ? and pswrd = ?");
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, pswrd);
            resultSet = preparedStatement.executeQuery();
 
            while (resultSet.next()) {
            	salesman.setId(resultSet.getInt("id"));
            	salesman.setName(resultSet.getString("name"));
            	salesman.setPswrd(resultSet.getString("pswrd"));
            	salesman.setRole(resultSet.getString("role"));
            	salesman.setIsConnected(resultSet.getInt("is_connected"));
            	
            }
 
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            //ConnectionManager.closeConnection(connection);
        }
 
        return salesman;
    }
	
	//Read All
	public List<Salesman> selectAll() {
		Statement statement = null;
		ResultSet resultSet = null;
		List<Salesman> salesmans = new LinkedList<Salesman>();
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("SELECT * FROM tblSalesmans");

			Salesman salesman = null;
			while(resultSet.next()){
				salesman = new Salesman();
				salesman.setId(Integer.parseInt(resultSet.getString("id")));
				salesman.setName(resultSet.getString("name"));
				salesman.setPswrd(resultSet.getString("pswrd"));
				salesman.setRole(resultSet.getString("role"));
				salesman.setIsConnected(Integer.parseInt(resultSet.getString("is_connected")));
				
				
				salesmans.add(salesman);
			}

			/*resultSet.close();
			statement.close();*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
		System.out.println(salesmans);
		return salesmans;
	}


	//Update
	public void update(/*Salesman salesman, int id*/) {
		Salesman salesman = null;
		try {
			Object deserializedObject = SerializationUtil.deserialize("c:\\editSalesman.out");
			salesman = (Salesman) deserializedObject;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement("UPDATE tblSalesmans SET " +
					"name = ?, pswrd = ?, role = ?, is_connected = ? WHERE id = ?");

			preparedStatement.setString(1, salesman.getName());
			preparedStatement.setString(2, salesman.getPswrd());
			preparedStatement.setString(3, salesman.getRole());
			preparedStatement.setInt(4, salesman.getIsConnected());
			preparedStatement.setInt(5, salesman.getId());
			preparedStatement.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
	}

	//Delete
	public void delete(int id) {
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement("DELETE FROM tblSalesmans WHERE id = ?");
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();


		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
	}
}
